insert overwrite table bidefault.dm_prescription_reach_details_dt partition (dt)
select taking_date
     , island
     , waybill_no
     , date_add(plan_sign_day, nvl(t_special.extra_time, 0))                      plan_sign_day
     , island_code
     , island_name
     , customer_code
     , customer_name
     , send_time
     , start_manage_regional_code
     , start_manage_regional_name
     , start_subordinate_agent_code
     , start_subordinate_agent_name
     , start_parent_network_code
     , start_parent_network_name
     , start_regional_id
     , start_regional_desc
     , start_provider_id
     , start_provider_desc
     , start_city_id
     , start_city_desc
     , start_area_id
     , start_area_desc
     , start_pick_network_code
     , start_pick_network_name
     , sign_manage_regional_code
     , sign_manage_regional_name
     , sign_subordinate_agent_code
     , sign_subordinate_agent_name
     , sign_parent_network_code
     , sign_parent_network_name
     , sign_regional_id
     , sign_regional_desc
     , sign_provider_id
     , sign_provider_desc
     , sign_city_id
     , sign_city_desc
     , sign_area_id
     , sign_area_desc
     , sign_network_code
     , sign_network_name
     , taking_time
     , start_center_code
     , start_center_name
     , start_centerarriva_departure_time
     , start_centerarriva_time
     , start_centersend_departure_time
     , departure_task
     , sign_center_code
     , sign_center_name
     , sign_centersend_departure_time
     , sign_centerarrival_departure_time
     , sign_centersend_time
     , cargo_time
     , arrival_shipment_name
     , pres_sign_time
     , real_sign_time
     , is_pres_sign
     , is_real_sign
     , is_delivery_delay
     , is_in_delay
     , is_out_delay
     , aging_sign_count_type
     , is_trunk_delay
     , districtprescription
     , case
           when real_sign_time is null and datediff(current_date(), taking_date) >
                                           districtprescription + nvl(t_special.extra_time, 0) then 0
           when real_sign_time is null and datediff(current_date(), taking_date) <=
                                           districtprescription + nvl(t_special.extra_time, 0) then null
           when real_sign_time is not null and datediff(real_sign_time, taking_date) <=
                                               districtprescription + nvl(t_special.extra_time, 0) then 1
           when real_sign_time is not null and datediff(real_sign_time, taking_date) >
                                               districtprescription + nvl(t_special.extra_time, 0) then 0
    end as                                                                        is_real_standard
     , case
           when pres_sign_time is null and datediff(current_date(), taking_date) >
                                           districtprescription + nvl(t_special.extra_time, 0) then 0
           when pres_sign_time is null and datediff(current_date(), taking_date) <=
                                           districtprescription + nvl(t_special.extra_time, 0) then null
           when pres_sign_time is not null and datediff(pres_sign_time, taking_date) <=
                                               districtprescription + nvl(t_special.extra_time, 0) then 1
           when pres_sign_time is not null and datediff(pres_sign_time, taking_date) >
                                               districtprescription + nvl(t_special.extra_time, 0) then 0
    end as                                                                        is_pres_standard
     , concat(date_add(plan_sign_day, nvl(t_special.extra_time, 0)), ' 23:59:59') plan_sign_date
     , economic_circle_type
     , platform_effect
     , is_platform_real_standard
     , is_platform_pres_standard
     , platform_effect_type
     , platform_name
     , plan_platform_date
     , mountain_platform_effect
     , mountain_is_platform_real_standard
     , mountain_is_platform_pres_standard
     , mountain_platform_effect_type
     , mountain_platform_name
     , plan_mountain_platform_date
     , free_platform_effect
     , free_is_platform_real_standard
     , free_is_platform_pres_standard
     , free_platform_effect_type
     , free_platform_name
     , plan_free_platform_date
     , jd_platform_effect
     , jd_is_platform_real_standard
     , jd_is_platform_pres_standard
     , jd_platform_effect_type
     , jd_platform_name
     , plan_jd_platform_date
     , cainiao_platform_effect
     , cainiao_is_platform_real_standard
     , cainiao_is_platform_pres_standard
     , cainiao_platform_effect_type
     , cainiao_platform_name
     , plan_cainiao_platform_date
     , cainiao_pro_platform_effect
     , cainiao_pro_is_platform_real_standard
     , cainiao_pro_is_platform_pres_standard
     , cainiao_pro_platform_effect_type
     , cainiao_pro_platform_name
     , plan_cainiao_pro_platform_date
     , express_type_code
     , express_type_name
     , collect_send_in_time
     , start_unloading_shipment_no
     , end_unloading_shipment_no
     , route_send_time
     , cainiao_slower_pre
     , cainiao_pro_taking_slot
     , order_mark_field_name
     , t_special.extra_time
     , cainiao_pro_real_effect_diff
     , cainiao_pro_pres_effect_diff
     , mountain_pro_platform_effect
     , mountain_pro_is_platform_real_standard
     , mountain_pro_is_platform_pres_standard
     , mountain_pro_platform_effect_type
     , mountain_pro_platform_name
     , plan_mountain_pro_platform_date
     , dt
from bidefault.dm_prescription_reach_pre_details_dt detail
         left join (select billcode, max(nvl(ceil(cast(special_extra_time as decimal(16, 4))), 0)) extra_time
                    from jms_dwd.dwd_terminal_sign_all_detail_new_dt
                    where dt >= date_sub('{{ execution_date | cst_ds }}',10)
                      and dt <= '{{ execution_date | cst_ds }}'
                      and special_extra_time > 0
                    group by billcode) t_special
                   on detail.waybill_no = t_special.billcode
where detail.dt >= date_sub('{{ execution_date | cst_ds }}',10)
  and detail.dt <= '{{ execution_date | cst_ds }}'
    distribute by dt
    , abs(hash(waybill_no)) % 160;


insert overwrite table bidefault.dm_prescription_reach_details_plan_dt partition (dt)
select plan_sign_day                         --" 规划应签收日期",
     , island                                -- "订单来源",
     , waybill_no                            --" 运单号",
     , taking_date                           --"揽收日期",
     , island_code                           --"订单来源code",
     , island_name                           --"订单来源名称",
     , customer_code                         --" 客户编号",
     , customer_name                         --" 客户名称",
     , send_time                             --" 发件时间",
     , start_manage_regional_code            --" 始发管理大区code",
     , start_manage_regional_name            --" 始发管理大区name",
     , start_subordinate_agent_code          --" 始发代理区code",
     , start_subordinate_agent_name          --" 始发代理区name",
     , start_parent_network_code             --" 始发加盟商code",
     , start_parent_network_name             --" 始发加盟商name",
     , start_regional_id                     --" 始发大区id",
     , start_regional_desc                   --" 始发大区name",
     , start_provider_id                     --" 始发省份id",
     , start_provider_desc                   --" 始发省份name",
     , start_city_id                         --" 始发城市id",
     , start_city_desc                       --" 始发城市name",
     , start_area_id                         --" 始发区id",
     , start_area_desc                       --" 始发区name",
     , start_pick_network_code               --" 始发网点code",
     , start_pick_network_name               --" 始发网点name",
     , sign_manage_regional_code             --"签收管理大区code",
     , sign_manage_regional_name             --"签收管理大区name",
     , sign_subordinate_agent_code           --" 签收代理区code",
     , sign_subordinate_agent_name           --" 签收代理区name",
     , sign_parent_network_code              --" 签收加盟商code",
     , sign_parent_network_name              --" 签收加盟商name",
     , sign_regional_id                      --" 签收大区id",
     , sign_regional_desc                    --" 签收大区name",
     , sign_provider_id                      --" 签收省份id",
     , sign_provider_desc                    --" 签收省份name",
     , sign_city_id                          --" 签收城市id",
     , sign_city_desc                        --" 签收城市name",
     , sign_area_id                          --" 签收区id",
     , sign_area_desc                        --" 签收区name",
     , sign_network_code                     --" 签收网点code",
     , sign_network_name                     --" 签收网点name",
     , taking_time                           --" 揽收时间",
     , start_center_code                     --" 始发转运中心code",
     , start_center_name                     --" 始发转运中心name",
     , start_centerarriva_departure_time     --" 始发中心到件扫描时间",
     , start_centerarriva_time               --" 始发中心发件扫描时间",
     , start_centersend_departure_time       --"始发中心发车时间",
     , departure_task                        --" 始发发车任务单号",
     , sign_center_code                      --" 目的转运中心code",
     , sign_center_name                      --" 目的转运中心name",
     , sign_centersend_departure_time        --" 目的中心发车扫描时间",
     , sign_centerarrival_departure_time     --" 目的中心到车时间",
     , sign_centersend_time                  --" 目的中心发件扫描时间",
     , cargo_time                            --"应散货时间",
     , arrival_shipment_name                 --目的中心到车线路名称
     , pres_sign_time                        --" 时效签收时间",
     , real_sign_time                        --" 实际签收时间",
     , is_pres_sign                          -- "时效签收",
     , is_real_sign                          -- "实际签收",
     , is_delivery_delay                     -- "交件是否准点	 0否1是",
     , is_in_delay                           -- "出港是否及时 0否1是",
     , is_out_delay                          -- "进港转运是否及时 0否1是",
     , aging_sign_count_type                 --时效签收是否晚点
     , is_trunk_delay                        -- "干线班次是否及时 0否1是",
     , districtprescription                  -- "区县时效",
     , is_real_standard                      -- "实际是否达标  1是0否",
     , is_pres_standard                      -- "时效是否达标  1是0否",
     , plan_sign_date                        --"规划应签收时间",
     , economic_circle_type                  -- "经济圈名称 经济圈名称 1珠三角 2长三角 3京津冀 空代表不属于经济圈",
     , platform_effect                       -- "桃花岛平台时效",
     , is_platform_real_standard             -- "桃花岛平台实际是否达标  1是0否",
     , is_platform_pres_standard             -- "桃花岛平台时效是否达标  1是0否",
     , platform_effect_type                  -- "是否桃花岛平台时效 1是0否",
     , platform_name                         --"桃花岛平台名称",
     , plan_platform_date                    --桃花岛规划应签收日期
     , mountain_platform_effect              --"紫金山平台时效",
     , mountain_is_platform_real_standard    --"紫金山平台实际是否达标  1是0否",
     , mountain_is_platform_pres_standard    --"紫金山平台时效是否达标  1是0否",
     , mountain_platform_effect_type         --"是否紫金山平台时效 1是0否",
     , mountain_platform_name                --"紫金山平台时效名称",
     , plan_mountain_platform_date           --紫金山规划应签收日期
     , free_platform_effect                  --"逍遥锋平台时效",
     , free_is_platform_real_standard        --"逍遥锋平台实际是否达标  1是0否",
     , free_is_platform_pres_standard        --"逍遥锋平台时效是否达标  1是0否",
     , free_platform_effect_type             --"是否逍遥锋平台时效 1是0否",
     , free_platform_name                    --"逍遥锋平台时效名称",
     , plan_free_platform_date               --逍遥锋规划应签收日期
     , jd_platform_effect                    --"京东平台时效",
     , jd_is_platform_real_standard          --"京东平台实际是否达标  1是0否",
     , jd_is_platform_pres_standard          --"京东平台时效是否达标  1是0否",
     , jd_platform_effect_type               --"是否京东平台时效 1是0否",
     , jd_platform_name                      --"京东平台时效名称",
     , plan_jd_platform_date                 --京东规划应签收日期
     , cainiao_platform_effect               --"菜鸟平台时效",
     , cainiao_is_platform_real_standard     --"菜鸟平台实际是否达标  1是0否",
     , cainiao_is_platform_pres_standard     --"菜鸟平台时效是否达标  1是0否",
     , cainiao_platform_effect_type          --"是否菜鸟平台时效 1是0否",
     , cainiao_platform_name                 --"菜鸟平台时效名称",
     , plan_cainiao_platform_date            --菜鸟规划应签收日期
     , cainiao_pro_platform_effect           --"菜鸟超时平台时效",
     , cainiao_pro_is_platform_real_standard --"菜鸟超时平台实际是否达标 1是0否",
     , cainiao_pro_is_platform_pres_standard --"菜鸟超时平台时效是否达标 1是0否",
     , cainiao_pro_platform_effect_type      --"是否菜鸟超时平台时效 1是0否",
     , cainiao_pro_platform_name             --"是否菜鸟超时平台时效 1是0否"
     , plan_cainiao_pro_platform_date        --菜鸟超时规划应签收日期
     , express_type_code
     , express_type_name
     , collect_send_in_time
     , start_unloading_shipment_no
     , end_unloading_shipment_no
     , route_send_time
     , cainiao_slower_pre
     , cainiao_pro_taking_slot
     , order_mark_field_name
     , extra_time
     , cainiao_pro_real_effect_diff
     , cainiao_pro_pres_effect_diff
     , mountain_pro_platform_effect
     , mountain_pro_is_platform_real_standard
     , mountain_pro_is_platform_pres_standard
     , mountain_pro_platform_effect_type
     , mountain_pro_platform_name
     , plan_mountain_pro_platform_date
     , plan_sign_day as dt
from bidefault.dm_prescription_reach_details_dt
where dt >= date_format(date_add(date_sub('{{ execution_date | cst_ds }}',10), -7), 'yyyy-MM-dd')
  and dt <= '{{ execution_date | cst_ds }}'
  and from_unixtime(unix_timestamp(plan_sign_date), 'yyyy-MM-dd') >= date_sub('{{ execution_date | cst_ds }}',10)
  and from_unixtime(unix_timestamp(plan_sign_date), 'yyyy-MM-dd') <= date_add('{{ execution_date | cst_ds }}', 3)
    distribute by dt
    , abs(hash(waybill_no)) % 160;


insert overwrite table bidefault.dm_prescription_reach_details_sign_dt partition (dt)
select date(pres_sign_time)                  --" 规划应签收日期",
     , island                                -- "订单来源",
     , waybill_no                            --" 运单号",
     , taking_date                           --"揽收日期",
     , island_code                           --"订单来源code",
     , island_name                           --"订单来源名称",
     , customer_code                         --" 客户编号",
     , customer_name                         --" 客户名称",
     , send_time                             --" 发件时间",
     , start_manage_regional_code            --" 始发管理大区code",
     , start_manage_regional_name            --" 始发管理大区name",
     , start_subordinate_agent_code          --" 始发代理区code",
     , start_subordinate_agent_name          --" 始发代理区name",
     , start_parent_network_code             --" 始发加盟商code",
     , start_parent_network_name             --" 始发加盟商name",
     , start_regional_id                     --" 始发大区id",
     , start_regional_desc                   --" 始发大区name",
     , start_provider_id                     --" 始发省份id",
     , start_provider_desc                   --" 始发省份name",
     , start_city_id                         --" 始发城市id",
     , start_city_desc                       --" 始发城市name",
     , start_area_id                         --" 始发区id",
     , start_area_desc                       --" 始发区name",
     , start_pick_network_code               --" 始发网点code",
     , start_pick_network_name               --" 始发网点name",
     , sign_manage_regional_code             --"签收管理大区code",
     , sign_manage_regional_name             --"签收管理大区name",
     , sign_subordinate_agent_code           --" 签收代理区code",
     , sign_subordinate_agent_name           --" 签收代理区name",
     , sign_parent_network_code              --" 签收加盟商code",
     , sign_parent_network_name              --" 签收加盟商name",
     , sign_regional_id                      --" 签收大区id",
     , sign_regional_desc                    --" 签收大区name",
     , sign_provider_id                      --" 签收省份id",
     , sign_provider_desc                    --" 签收省份name",
     , sign_city_id                          --" 签收城市id",
     , sign_city_desc                        --" 签收城市name",
     , sign_area_id                          --" 签收区id",
     , sign_area_desc                        --" 签收区name",
     , sign_network_code                     --" 签收网点code",
     , sign_network_name                     --" 签收网点name",
     , taking_time                           --" 揽收时间",
     , start_center_code                     --" 始发转运中心code",
     , start_center_name                     --" 始发转运中心name",
     , start_centerarriva_departure_time     --" 始发中心到件扫描时间",
     , start_centerarriva_time               --" 始发中心发件扫描时间",
     , start_centersend_departure_time       --"始发中心发车时间",
     , departure_task                        --" 始发发车任务单号",
     , sign_center_code                      --" 目的转运中心code",
     , sign_center_name                      --" 目的转运中心name",
     , sign_centersend_departure_time        --" 目的中心发车扫描时间",
     , sign_centerarrival_departure_time     --" 目的中心到车时间",
     , sign_centersend_time                  --" 目的中心发件扫描时间",
     , cargo_time                            --"应散货时间",
     , arrival_shipment_name                 --目的中心到车线路名称
     , pres_sign_time                        --" 时效签收时间",
     , real_sign_time                        --" 实际签收时间",
     , is_pres_sign                          -- "时效签收",
     , is_real_sign                          -- "实际签收",
     , is_delivery_delay                     -- "交件是否准点	 0否1是",
     , is_in_delay                           -- "出港是否及时 0否1是",
     , is_out_delay                          -- "进港转运是否及时 0否1是",
     , aging_sign_count_type                 --时效签收是否晚点
     , is_trunk_delay                        -- "干线班次是否及时 0否1是",
     , districtprescription                  -- "区县时效",
     , is_real_standard                      -- "实际是否达标  1是0否",
     , is_pres_standard                      -- "时效是否达标  1是0否",
     , plan_sign_date                        --"规划应签收时间",
     , economic_circle_type                  -- "经济圈名称 经济圈名称 1珠三角 2长三角 3京津冀 空代表不属于经济圈",
     , platform_effect                       -- "桃花岛平台时效",
     , is_platform_real_standard             -- "桃花岛平台实际是否达标  1是0否",
     , is_platform_pres_standard             -- "桃花岛平台时效是否达标  1是0否",
     , platform_effect_type                  -- "是否桃花岛平台时效 1是0否",
     , platform_name                         --"桃花岛平台名称",
     , plan_platform_date                    --桃花岛规划应签收日期
     , mountain_platform_effect              --"紫金山平台时效",
     , mountain_is_platform_real_standard    --"紫金山平台实际是否达标  1是0否",
     , mountain_is_platform_pres_standard    --"紫金山平台时效是否达标  1是0否",
     , mountain_platform_effect_type         --"是否紫金山平台时效 1是0否",
     , mountain_platform_name                --"紫金山平台时效名称",
     , plan_mountain_platform_date           --紫金山规划应签收日期
     , free_platform_effect                  --"逍遥锋平台时效",
     , free_is_platform_real_standard        --"逍遥锋平台实际是否达标  1是0否",
     , free_is_platform_pres_standard        --"逍遥锋平台时效是否达标  1是0否",
     , free_platform_effect_type             --"是否逍遥锋平台时效 1是0否",
     , free_platform_name                    --"逍遥锋平台时效名称",
     , plan_free_platform_date               --逍遥锋规划应签收日期
     , jd_platform_effect                    --"京东平台时效",
     , jd_is_platform_real_standard          --"京东平台实际是否达标  1是0否",
     , jd_is_platform_pres_standard          --"京东平台时效是否达标  1是0否",
     , jd_platform_effect_type               --"是否京东平台时效 1是0否",
     , jd_platform_name                      --"京东平台时效名称",
     , plan_jd_platform_date                 --京东规划应签收日期
     , cainiao_platform_effect               --"菜鸟平台时效",
     , cainiao_is_platform_real_standard     --"菜鸟平台实际是否达标  1是0否",
     , cainiao_is_platform_pres_standard     --"菜鸟平台时效是否达标  1是0否",
     , cainiao_platform_effect_type          --"是否菜鸟平台时效 1是0否",
     , cainiao_platform_name                 --"菜鸟平台时效名称",
     , plan_cainiao_platform_date            --菜鸟规划应签收日期
     , cainiao_pro_platform_effect           --"菜鸟超时平台时效",
     , cainiao_pro_is_platform_real_standard --"菜鸟超时平台实际是否达标 1是0否",
     , cainiao_pro_is_platform_pres_standard --"菜鸟超时平台时效是否达标 1是0否",
     , cainiao_pro_platform_effect_type      --"是否菜鸟超时平台时效 1是0否",
     , cainiao_pro_platform_name             --"是否菜鸟超时平台时效 1是0否"
     , plan_cainiao_pro_platform_date        --菜鸟超时规划应签收日期
     , express_type_code
     , express_type_name
     , collect_send_in_time
     , start_unloading_shipment_no
     , end_unloading_shipment_no
     , route_send_time
     , cainiao_slower_pre
     , cainiao_pro_taking_slot
     , order_mark_field_name
     , extra_time
     , cainiao_pro_real_effect_diff
     , cainiao_pro_pres_effect_diff
     , mountain_pro_platform_effect
     , mountain_pro_is_platform_real_standard
     , mountain_pro_is_platform_pres_standard
     , mountain_pro_platform_effect_type
     , mountain_pro_platform_name
     , plan_mountain_pro_platform_date
     , date(pres_sign_time) as dt
from bidefault.dm_prescription_reach_details_dt
where dt >= date_format(date_add(date_sub('{{ execution_date | cst_ds }}',10), -22), 'yyyy-MM-dd')
  and dt <= '{{ execution_date | cst_ds }}'
  and from_unixtime(unix_timestamp(pres_sign_time), 'yyyy-MM-dd') >= date_sub('{{ execution_date | cst_ds }}',10)
  and from_unixtime(unix_timestamp(pres_sign_time), 'yyyy-MM-dd') <= '{{ execution_date | cst_ds }}'
    distribute by dt
    , abs(hash(waybill_no)) % 160;